package com.ttloner4.dbutils;

import com.sun.org.apache.bcel.internal.generic.NEW;
import com.ttloner1.util.JDBCUtils;
import com.ttloner2.bean.Customer;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author TTLoner
 * @date 2021/6/25 20:29
 * @description
 * dbutils Apache的工具类库
 */
public class QueryRunnerTest {
    //修改
    @Test
    public void test() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConnection();
        String sql="insert into customers(name,email,birth)values(?,?,?)";
        queryRunner.update(conn,sql,"鸡你太美","caixukun.com","1997-12-13");
        JDBCUtils.closeConnection(conn,null);
    }
    //查询
    @Test
    public void test2() throws SQLException {
        QueryRunner qr = new QueryRunner();
        BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
        String sql="select id,name,email from customers where id<?";
        Connection conn = JDBCUtils.getConnection();
        List<Customer> query = qr.query(conn, sql, handler, 20);
        for(Customer c:query) System.out.println(c);
    }
    //MapHandler
    @Test
    public void test3() throws SQLException {
        QueryRunner qr = new QueryRunner();
        Connection conn=JDBCUtils.getConnection();
        String sql="select id,name,email from customers where id<?";
        MapListHandler handler = new MapListHandler();
        List<Map<String, Object>> query = qr.query(conn, sql, handler, 20);
        System.out.println(query);
    }
    //ScalarHander 查询特殊值
    @Test
    public void test4() throws SQLException {
        QueryRunner qr = new QueryRunner();
        Connection conn=JDBCUtils.getConnection();
        String sql="select count(*) from customers";
        ScalarHandler handler = new ScalarHandler<Integer>();
        Object o = qr.query(conn, sql, handler);
        System.out.println(o);
    }
    //自定义resulthandler实现类
    @Test
    public void test5() throws SQLException {
        QueryRunner qr = new QueryRunner();
        Connection conn=JDBCUtils.getConnection();
        String sql="select * from customers where id=?";
        ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
            @Override
            public Customer handle(ResultSet resultSet) throws SQLException {
               //此处重写hander方法，return值为query的值,假设我想只返回id,name,email
                if(!resultSet.next()) return null;
                Customer customer = new Customer();
                Object name = resultSet.getObject("name");
                Object id = resultSet.getObject("id");
                Object email = resultSet.getObject("email");
                customer.setName((String) name);
                customer.setId((Integer) id);
                customer.setEmail((String) email);
                return customer;
            }
        };
        Customer query = qr.query(conn, sql, handler, 20);
        System.out.println(query);
    }
    /*
     * @author: TTLoner
     * @time: 2021/6/25 22:52
     * @description:
     *最终操作，关闭流
     */
    @Test
    public void close(){
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
}
